import base64
import os
from PyQt5.QtSql import QSqlQuery, QSqlDatabase
import time


class MysqlLite:
    def __init__(self):
        try:
            self.database = QSqlDatabase.addDatabase('QSQLITE')
            # 使用绝对路径避免路径问题
            db_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "database", "shop.db")
            print(f"数据库路径: {db_path}")

            # 设置超时时间，避免长时间等待
            self.database.setDatabaseName(db_path)
            self.database.setConnectOptions("QSQLITE_ENABLE_QT_CONCURRENT=1")

            # 增加重试机制
            max_retries = 3
            for attempt in range(max_retries):
                if self.database.open():
                    print("数据库连接成功")
                    # 设置 PRAGMA 以确保 UTF-8 编码和外键约束
                    self.operation_sql("PRAGMA encoding = 'UTF-8';")
                    self.operation_sql("PRAGMA foreign_keys = ON;")
                    break
                else:
                    error_text = self.database.lastError().text()
                    print(f"第 {attempt + 1} 次尝试连接失败: {error_text}")
                    if attempt < max_retries - 1:
                        time.sleep(0.5)  # 等待0.5秒后重试
            else:
                print("无法连接数据库，所有重试都失败了")

        except Exception as e:
            print("无法连接数据库", e)

    def __del__(self):
        if self.database.isOpen():
            self.database.close()

    def operation_sql(self, sql, params=None):
        # 增加重试机制
        max_retries = 3
        for attempt in range(max_retries):
            self.query = QSqlQuery()
            if not self.query.prepare(sql):
                print(f"SQL准备失败：{sql}")
                print(f"错误原因：{self.query.lastError().text()}")
                return False

            if params:
                for param in params:
                    self.query.addBindValue(param)

            success = self.query.exec_()
            if success:
                if os.getenv("DEBUG", "False").lower() == "true":
                    print(f"执行SQL：{sql}，参数：{params}")
                return True
            else:
                error_text = self.query.lastError().text()
                print(f"第 {attempt + 1} 次尝试执行SQL失败：{sql}")
                print(f"错误原因：{error_text}")

                if attempt < max_retries - 1:
                    time.sleep(0.5)  # 等待0.5秒后重试
                else:
                    return False

        return False

    def selectData(self, sql, params=None):
        # 增加重试机制
        max_retries = 3
        for attempt in range(max_retries):
            self.query = QSqlQuery()
            if not self.query.prepare(sql):
                print(f"SQL准备失败：{sql}")
                print(f"错误原因：{self.query.lastError().text()}")
                return []

            if params:
                for param in params:
                    self.query.addBindValue(param)

            if not self.query.exec_():
                error_text = self.query.lastError().text()
                print(f"第 {attempt + 1} 次尝试执行查询失败：{sql}")
                print(f"错误原因：{error_text}")

                if attempt < max_retries - 1:
                    time.sleep(0.5)  # 等待0.5秒后重试
                else:
                    return []
            else:
                result = []
                record = self.query.record()
                columns = [record.fieldName(i) for i in range(record.count())]
                while self.query.next():
                    row = {}
                    for i, column in enumerate(columns):
                        row[column] = self.query.value(i)
                    result.append(row)

                if os.getenv("DEBUG", "False").lower() == "true":
                    print(f"查询 {sql} 结果: {result}")
                return result

        return []

    def execute(self, sql, params=None):
        # 增加重试机制
        max_retries = 3
        for attempt in range(max_retries):
            query = QSqlQuery()
            if params:
                if not query.prepare(sql):
                    print(f"SQL准备失败：{sql}")
                    print(f"错误原因：{query.lastError().text()}")
                    return False
                for param in params:
                    query.addBindValue(param)
            else:
                query = QSqlQuery(sql)

            success = query.exec_()
            if success:
                if os.getenv("DEBUG", "False").lower() == "true":
                    print(f"执行SQL：{sql}，参数：{params}")
                return self._handle_query_result(query)
            else:
                error_text = query.lastError().text()
                print(f"第 {attempt + 1} 次尝试执行SQL失败：{sql}")
                print(f"错误原因：{error_text}")

                if attempt < max_retries - 1:
                    time.sleep(0.5)  # 等待0.5秒后重试
                else:
                    return False

        return False

    def _handle_query_result(self, query):
        result = []
        record = query.record()
        columns = [record.fieldName(i) for i in range(record.count())]
        while query.next():
            row = {}
            for i, column in enumerate(columns):
                row[column] = query.value(i)
            result.append(row)
        return result if result else True

    def transaction(self):
        return self.database.transaction()

    def commit(self):
        return self.database.commit()

    def rollback(self):
        return self.database.rollback()


# 示例数据，不指定 id 字段，依赖 AUTOINCREMENT
products = [
    {
        "name": "iPhone 15 Pro",
        "description": "最新款苹果手机，搭载A17 Pro芯片",
        "price": 8999.00,
        "quantity": 50,
        "category_id": 1
    },
    {
        "name": "MacBook Air M2",
        "description": "轻薄便携的笔记本电脑",
        "price": 9999.00,
        "quantity": 30,
        "category_id": 1
    },
    {
        "name": "AirPods Pro",
        "description": "苹果无线耳机，主动降噪",
        "price": 1999.00,
        "quantity": 100,
        "category_id": 1
    },
    {
        "name": "小米13",
        "description": "小米旗舰手机，骁龙8 Gen2",
        "price": 3999.00,
        "quantity": 0,
        "category_id": 1
    },
    {
        "name": "华为MateBook",
        "description": "华为笔记本电脑，14英寸",
        "price": 5999.00,
        "quantity": 30,
        "category_id": 1
    },
    {
        "name": "男士T恤",
        "description": "纯棉舒适T恤，多色可选",
        "price": 89.00,
        "quantity": 200,
        "category_id": 2
    },
    {
        "name": "女士连衣裙",
        "description": "夏季清新连衣裙",
        "price": 299.00,
        "quantity": 150,
        "category_id": 2
    },
    {
        "name": "运动鞋",
        "description": "透气舒适运动鞋",
        "price": 399.00,
        "quantity": 120,
        "category_id": 2
    },
    {
        "name": "牛仔裤",
        "description": "经典款牛仔裤",
        "price": 199.00,
        "quantity": 180,
        "category_id": 2
    },
    {
        "name": "羽绒服",
        "description": "保暖羽绒服，防风防水",
        "price": 599.00,
        "quantity": 80,
        "category_id": 2
    }
]
default_categories = [
    ("电子产品", "各种电子设备和配件"),
    ("服装", "男女服装和配饰"),
    ("食品", "各种食品和饮料"),
    ("图书", "各类图书和杂志"),
    ("家居用品", "家庭日用品和装饰"),
    ("运动用品", "运动器材和户外用品")
]

import json

def init_db():
    my_sqlite = MysqlLite()
    my_sqlite.operation_sql("PRAGMA foreign_keys = ON;")
    tables = my_sqlite.execute("SELECT name FROM sqlite_master WHERE type='table';")
    existing_tables = [table['name'] for table in tables] if isinstance(tables, list) else []
    print("现有表:", existing_tables)

    if 'users' not in existing_tables:
        my_sqlite.operation_sql(
            "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE, password TEXT NOT NULL, is_admin INTEGER NOT NULL DEFAULT 0)"
        )
        print("创建 users 表成功")
        my_sqlite.operation_sql(
            "INSERT OR IGNORE INTO users (username, email, password, is_admin) VALUES (?, ?, ?, ?)",
            ['gscsd', '321321@qq.com', '321321', 0]
        )
        print("插入 users 数据成功")

    if 'categories' not in existing_tables:
        my_sqlite.operation_sql(
            "CREATE TABLE categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, description TEXT)"
        )
        print("创建 categories 表成功")
        for category in default_categories:
            my_sqlite.operation_sql(
                "INSERT OR IGNORE INTO categories (name, description) VALUES (?, ?)",
                [category[0], category[1]]
            )
        print("插入 categories 数据成功")

    if 'products' not in existing_tables:
        my_sqlite.operation_sql(
            "CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, description TEXT, price REAL NOT NULL, quantity INTEGER NOT NULL DEFAULT 0, category_id INTEGER, image_base64 TEXT, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL)"
        )
        print("创建 products 表成功")
        category_id_map = {}
        categories = my_sqlite.execute("SELECT id, name FROM categories")
        for cat in categories:
            category_id_map[cat['name']] = cat['id']

        base_dir = os.path.dirname(os.path.abspath(__file__))
        project_root = os.path.dirname(os.path.dirname(base_dir))
        image_dir = os.path.join(project_root, 'pyqt', 'images', 'products')

        for product in products:
            category_id = category_id_map.get(default_categories[product['category_id'] - 1][0], 1)
            image_path = os.path.join(image_dir, f"{product['name'].lower().replace(' ', '_')}.png")
            image_base64 = None
            if os.path.exists(image_path):
                print(f"找到图片: {image_path}")
                try:
                    with open(image_path, "rb") as image_file:
                        image_base64 = base64.b64encode(image_file.read()).decode('utf-8')
                except Exception as e:
                    print(f"将图片 {image_path} 转换为 Base64 失败: {e}")
            else:
                print(f"图片不存在: {image_path}")
            my_sqlite.operation_sql(
                "INSERT OR IGNORE INTO products (name, description, price, quantity, category_id, image_base64) VALUES (?, ?, ?, ?, ?, ?)",
                [product['name'], product['description'], product['price'], product['quantity'], category_id, image_base64]
            )
        print("插入 products 数据成功")

    if 'orders' not in existing_tables:
        my_sqlite.operation_sql(
            "CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_name TEXT NOT NULL, customer_email TEXT NOT NULL, order_date TEXT NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, price REAL NOT NULL)"
        )
        print("创建 orders 表成功")

    # 选择性查询字段
    print("用户表数据：")
    users_data = my_sqlite.selectData("SELECT id, username, email, is_admin FROM users")
    if users_data:
        print(json.dumps(users_data, ensure_ascii=False, indent=2))
    else:
        print("无用户数据")

    print("分类表数据：")
    categories_data = my_sqlite.selectData("SELECT id, name, description FROM categories")
    if categories_data:
        print(json.dumps(categories_data, ensure_ascii=False, indent=2))
    else:
        print("无分类数据")

    print("商品表数据：")
    products_data = my_sqlite.selectData("SELECT id, name, description, price, quantity, category_id FROM products")
    if products_data:
        print(json.dumps(products_data, ensure_ascii=False, indent=2))
    else:
        print("无商品数据")

def get_db():
    my_sqlite = MysqlLite()
    my_sqlite.operation_sql("PRAGMA foreign_keys = ON;")
    return my_sqlite

def get_db_info():
    # 获取所有表名
    db = get_db()
    tables = db.execute("SELECT name AS table_name FROM sqlite_master WHERE type = 'table' ORDER BY name;")
    for table in tables:
        table_name = table['table_name']
        # 获取表中的字段
        results = db.execute(f"PRAGMA table_info({table_name});")
        table['columns'] = [result['name'] for result in results]
    return tables


def check_database_status(self):
    """检查数据库状态"""
    try:
        # 尝试执行一个简单的查询
        self.operation_sql("SELECT 1")
        return True
    except Exception as e:
        print(f"数据库状态检查失败: {e}")
        return False


if __name__ == '__main__':
    init_db()